
use serde::{Deserialize, Serialize};
use sqlx::sqlite::SqlitePool;
use sqlx::{migrate::MigrateDatabase, Sqlite};
use std::fmt::Debug;
use anyhow::Result;
use tracing::{info, debug, error};

#[derive(Default, Serialize, Deserialize, Debug, Clone, sqlx::FromRow)]
#[sqlx(rename_all = "PascalCase")]
pub struct ChatHistory {  
    #[serde(default)]
    pub id: i32,
    #[serde(default)]
    pub question: String,    
    #[serde(default)]    
    pub timestamp: String,
    #[serde(default)]
    pub response: String,    
}

pub async fn sqlite_db_init(pool: &SqlitePool) -> anyhow::Result<()> {
    let sql = r#"
    CREATE TABLE chat(
        Id INTEGER PRIMARY KEY AUTOINCREMENT,
        Question CHAR(256) NOT NULL,       
        Response TEXT,       
        Timestamp CHAR(12) NOT NULL
        );"#;
    //info!("{}", sql);    
    let affect_r= sqlx::query(sql).execute(pool).await;
    if let Err(err) = affect_r {
        error!("create table error: {}", err);
        return Err(err.into());
    }
    //debug!("create table opens {:?}", affect_rows);    
    Ok(())
}
pub async fn create_db_pool(full_path_dbname:&str) -> Result<SqlitePool> {    
  // 数据库初始化
  let database_url = format!("sqlite:{}", full_path_dbname);      
  let mut db_init = false;
    if !Sqlite::database_exists(&database_url).await.unwrap_or(false) {
        // info!("Creating database {}", database_url);
        if let Err(err) = Sqlite::create_database(&database_url).await {
            panic!("error: {}", err);
        } else {
            db_init = true;
        }
    } else {            
        info!("Database already exists");
    }

    let pool = SqlitePool::connect(&database_url).await?;
    info!("Yes, connect {}", database_url);
    if db_init {
        sqlite_db_init(&pool).await?;
    } 
    Ok(pool)       
}

pub async fn save_chat2db(pool: &SqlitePool, quest: &str, ctime:&str, resp:&str) -> anyhow::Result<i32> {
    /* 
    let sql = "SELECT * FROM tasks WHERE Name=? limit 1";
    let rows = sqlx::query_as::<_, Task>(sql)
        .bind(name)
        .fetch_all(pool)
        .await?;   
    if rows.len() >= 1 {
        //  error!("cant open, already open id = {}", rows[0].id);
        return Ok(rows[0].id);
    }else{ */

    let cid =
        sqlx::query(r#"INSERT INTO chat(Question, Timestamp, Response) VALUES (?,?,?)"#)
            .bind(quest)                
            .bind(ctime)
            .bind(resp)
            .execute(pool)
            .await?
            .last_insert_rowid();
    Ok(cid as i32)    
}

pub async fn read_last_chats(pool: &SqlitePool, count:i32) -> anyhow::Result< Vec<ChatHistory>> {
    let sql = "SELECT * FROM chat ORDER BY Id Desc LIMIT ?";
    let rows = sqlx::query_as::<_, ChatHistory>(sql)
        .bind(count)
        .fetch_all(pool)
        .await?;      
    Ok(rows)
}
